
import MySQLdb
from minings.utils import mysqlrecord
from minings.factory import datapreprocess
from minings.utils import utility
from copy import deepcopy
import string,orange,orngMySQL,cPickle


class DataInformation:
    
    def __init__(self,**kwargs):
        self.fullname       = ""
        self.data           = None
        self.dataoption     = 1 #0:Whole classes except self.permremovelist,1:fixclass,2:both,3:all duals
        self.permremovelist = [""]
        self.fixedcls       = ""
        self.run            = None
        #Arbitrary properties
        self.Method         = None
        self.Class_X        = None
        self.Class_2        = None
        self.Class_3        = None
        self.Length         = None
        self.__dict__.update(kwargs)


class DP(object):
    
    def __init__(self,**kwargs):
        self.fullname       = ""
        self.data           = None
        self.dataoption     = 1 #0:Whole classes except self.permremovelist,1:fixclass,2:both,3:all duals
        self.permremovelist = [""]
        self.fixedcls       = ""
        self.run            = None
        self.__dict__.update(kwargs)
        self.getDatapackages()
        
    def getDatapackages(self):
        self.GD             = datapreprocess.DATAPREP(self)
        self.datapackages   = self.GD.datapackages


class ModifyGUT(object):
    
    def __init__(self,**kwargs):
        self.mainfullname           = "gut"
        self.databasename           = "gut"
        self.table_gut              = "gut"
        self.gut_colon2             = "gut_colon2"
        self.gut_colon3             = "gut_colon3"
        self.colon_both             = [self.gut_colon2,self.gut_colon3]
        self.__dict__.update(kwargs)
        self.maindataoption         = 1 #0:Whole classes except self.permremovelist,1:fixclass,2:both,3:all duals
        self.mainpermremovelist     = ["Polyp","Diverticulitis","Normal Pouch","Pouchitis","Alcoholic", "ALD", "Non ALD"]
        self.mainfixedcls           = "Healthy Control"
        self.mainrun                = utility.RUN()
        self.R                      = mysqlrecord.datasetMySQLrecord(databasename = self.databasename)
        self.t                      = orngMySQL.Connect('localhost','root',"dudesweet",self.databasename)
##        self.testprep()
##        self.testtable()
        self.deltable()
##        self.deltable_special()
##        self.modify()
##        self.checktable()
##        self.orangedata()

    def testprep(self):
        self.fullname           = "iris"
        self.data               = orange.ExampleTable("iris_d")
        self.dataoption         = 4 #0:Whole classes except self.permremovelist,1:fixclass,2:both,3:all duals,4:matrix
        self.permremovelist     = ["Iris-superbra"]
        self.fixedcls           = "Iris-axaxa"
        self.matrix             = [ ["Iris-axaxa","Iris-onemanshow"],
                                    ["Iris-setosa","Iris-versicolor","Iris-virginica"] ]
        self.run                = utility.RUN()
        self.GD                 = datapreprocess.DATAPREP(self)
        self.datapackages       = self.GD.datapackages
        for i in self.datapackages:
            print i.datasetname
            data = i.data
            print data.domain.classVar.values
            for cls,number in zip(data.domain.classVar.values,
                              orange.Distribution(data.domain.classVar,data)):
                print "%-24s: %s" % (cls,int(number))
            print "######################"
            
    def checktable(self):
        data       = self.t.query("""SELECT * FROM %s WHERE ( (%s in ("%s","%s","%s") and (%s!="%s" and %s!="%s")) or (%s in ("%s","%s")) )
                                                                             and %s="%s"
                                                   """ % (self.gut_colon2,  "c$Disease"     ,"Healthy Control_Ileum"    ,"Healthy Control_Colon"    ,"Healthy Control_Sigmoid"  , 
                                                                            "m$Class_2"     ,""                         ,"m$Class_3"                ,""                         ,  
                                                                            "c$Disease"     ,"Healthy Control_Lumen"    ,"Healthy Control_Stool"    ,
                                                                            "m$Method"      ,"Endoscopic"       ))
        for cls,number in zip(data.domain.classVar.values,
                                  orange.Distribution(data.domain.classVar,data)):
                print cls
                print number
##        print data.domain.classVar.values

    def deltable(self):
        self.R.cursor.execute("""SHOW TABLES""")
        row     = self.R.cursor.fetchall()
        kname   = "Tables_in_%s" % self.R.databasename
        for i in row:
##            if i[kname] not in []:
##            if i[kname] not in ["gut"]:
            if i[kname] not in ["gut","gut_colon2","gut_colon3","results"]:
                print i[kname]
                self.R.cursor.execute("""drop table %s""" % i[kname])
##        self.R.cursor.execute("""desc %s""" % self.gut_colon2)

    def deltable_special(self):
        self.R.cursor.execute("""select %s from %s""" % (self.R.tab_res.datasetname,self.R.tab_res.tname))
        row = self.R.cursor.fetchall()
        for i in row:
            delete  = False
            dname   = i[self.R.tab_res.datasetname]
            try:
                dname.index("gut_Endo_Stool")
                delete = True
            except ValueError:
                delete = False
            if delete == True:
                print dname
                self.R.cursor.execute("""delete from %s where %s="%s" """ % (self.R.tab_res.tname,self.R.tab_res.datasetname,dname))
                
    def orangedata(self):
        for i in range(len(self.colon_both)):
            data = self.t.query("""select * from %s""" % self.colon_both[i])
            data.save("%s.tab" % self.colon_both[i])
            
    def modify(self):
        try:
            for i in range(len(self.colon_both)):
                self.R.cursor.execute("""create table %s like %s
                                      """ % (self.colon_both[i],self.table_gut) )
                self.R.cursor.execute("""insert %s select * from %s
                                      """ % (self.colon_both[i],self.table_gut) )
                self.R.cursor.execute("""alter table %s modify %s TEXT
                                      """ % (self.colon_both[i],"c$Disease") )
                self.R.cursor.execute("""alter table %s add %s INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
                                      """ % (self.colon_both[i],"instance_id") )
                disease = {}
                self.R.cursor.execute("""select * from %s
                                      """ % (self.colon_both[i]) )
                row     = self.R.cursor.fetchall()
                for r in row:
                    if self.colon_both[i] == "gut_colon2":
                        ColonList   = ["Ascending_Colon","Transverse_Colon"]
                    elif self.colon_both[i] == "gut_colon3":
                        ColonList   = ["Ascending_Colon","Transverse_Colon","Sigmoid"]
                    if r["m$Class_X"] in ColonList:
                        modifyclass = "%s_%s" % (r["c$Disease"],"Colon")
                    else:
##                        if (r["m$Class_X"] != "" and r["m$Class_2"] != "" and r["m$Class_3"] != ""):
                        if (r["m$Class_X"] != ""):
                            modifyclass = "%s_%s" % (r["c$Disease"],r["m$Class_X"])
                        else:
                            modifyclass = "%s" % (r["c$Disease"])
                    try:
                        disease[modifyclass]
                    except KeyError:
                        disease[modifyclass] = None
                    self.R.cursor.execute("""update %s set %s="%s" where %s="%s"
                                          """ % (self.colon_both[i],"c$Disease",modifyclass,"instance_id",r["instance_id"] ) )
                self.R.cursor.execute("""alter table %s drop %s
                                          """ % (self.colon_both[i],"instance_id") )
                dkey        = disease.keys()
                a           = string.join(dkey,"','")
                classList   = "('%s')" % a
                self.R.cursor.execute("""alter table %s modify %s ENUM%s
                                          """ % (self.colon_both[i],"c$Disease",classList) )
        except MySQLdb.OperationalError,e:
            if e[0] != 1050:
                print e
                raise MySQLdb.OperationalError

            
if __name__ == "__main__":
    ModifyGUT()
